package com.autumn.mybatis.provider.util;

import com.autumn.mybatis.metadata.EntityColumn;
import com.autumn.mybatis.metadata.EntityTable;
import com.autumn.util.StringUtils;

import javax.persistence.GenerationType;

/**
 * 
 * @author 杨昌国
 *
 *         2017-10-19 15:08:54
 */
public class MybatisSqlUtils {

	/**
	 * 获取表或视图名称
	 * 
	 * @param table
	 *            表对象
	 * @return
	 */
	public static String getTableOrViewName(EntityTable table) {
		boolean isViewCode = table.isView() && !StringUtils.isNullOrBlank(table.getViewQueryStatement());
		if (isViewCode) {
			String viewCode = table.getViewQueryStatement().trim();
			if (!viewCode.startsWith("(")) {
				return String.format("(%s) AS %s", viewCode, table.getName());
			}
		}
		return table.getName();
	}

	/**
	 * 插入列集合(Insert into table())
	 * 
	 * @param table
	 *            表
	 * @param skipId
	 *            跳过 Id
	 * @param skipNullValue
	 *            跳过 null 值
	 * @return
	 */
	public static String insertColumns(EntityTable table, boolean skipId, boolean skipNullValue) {
		StringBuilder sql = new StringBuilder();
		sql.append("<trim prefix=\"(\" suffix=\")\" suffixOverrides=\",\">");
		for (EntityColumn column : table.getColumns()) {
			if (!column.isInsertable()) {
				continue;
			}
			if (skipId && column.isPrimaryKey()) {
				continue;
			}
			if (skipNullValue) {
				sql.append(MybatisXmlUtils.createIfTestValueNotNullNode(column, false, column.getColumnName(), false));
			} else {
				sql.append(column.getColumnName());
			}
			sql.append(",");
		}
		sql.append("</trim>");
		return sql.toString();
	}

	/**
	 * 插入值集合(values)
	 *
	 * @param table
	 *            表
	 * @param skipId
	 *            跳过 Id
	 * @param skipNullValue
	 *            跳过 null 值
	 * @return
	 */
	public static String insertValues(EntityTable table, boolean skipId, boolean skipNullValue) {
		StringBuilder sql = new StringBuilder();
		sql.append("<trim prefix=\"VALUES (\" suffix=\")\" suffixOverrides=\",\">");
		for (EntityColumn column : table.getColumns()) {
			if (!column.isInsertable()) {
				continue;
			}
			if (column.isPrimaryKey() && column.getGenerationType().equals(GenerationType.IDENTITY)) {
				sql.append("null");
			} else {
				sql.append(column.getColumnHolder());
			}
			sql.append(",");
		}
		sql.append("</trim>");
		return sql.toString();
	}

	/**
	 * 插入列与值
	 * 
	 * @param table
	 *            表
	 * @param skipId
	 *            跳过 Id
	 * @param skipNullValue
	 *            跳过 null 值
	 * @return
	 */
	public static String insert(EntityTable table, boolean skipId, boolean skipNullValue) {
		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO ");
		sql.append(getTableOrViewName(table));
		sql.append(" ");
		sql.append(insertColumns(table, skipId, skipNullValue));
		sql.append(insertValues(table, skipId, skipNullValue));
		return sql.toString();
	}

	/**
	 * 更新列集合
	 * 
	 * @param table
	 *            表
	 * @param skipNullValue
	 *            跳过null值
	 * @return
	 */
	public static String updateSetColumns(EntityTable table, boolean skipNullValue) {
		StringBuilder sql = new StringBuilder();
		sql.append("<trim prefix=\"SET\" suffixOverrides=\",\">");
		for (EntityColumn column : table.getColumns()) {
			if (column.isPrimaryKey() || !column.isUpdatable()) {
				continue;
			}
			if (skipNullValue) {
				sql.append(MybatisXmlUtils.createIfTestValueNotNullNode(column, false, column.getColumnEqualsHolder(),
						false));
			} else {
				sql.append(column.getColumnEqualsHolder());
				sql.append(",");
			}
		}
		sql.append("</trim>");
		return sql.toString();
	}

	/**
	 * 主键条件集合
	 * 
	 * @param table
	 *            表
	 * @return
	 */
	public static String wherePrimaryKeys(EntityTable table) {
		StringBuilder sql = new StringBuilder();
		sql.append("<where>");
		for (EntityColumn column : table.getColumns()) {
			if (column.isPrimaryKey()) {
				sql.append(" AND ");
				sql.append(column.getColumnEqualsHolder());
			}
		}
		sql.append("</where>");
		return sql.toString();
	}

	/**
	 * 获取主键值
	 * 
	 * @param table
	 * @param keyParamName
	 * @return
	 */
	public static String wherePrimaryKeys(EntityTable table, String keyParamName) {
		StringBuilder sql = new StringBuilder();
		sql.append("<where>");
		for (EntityColumn column : table.getColumns()) {
			if (column.isPrimaryKey()) {
				sql.append(" AND ");
				sql.append(String.format("%s", column.getColumnName()));
				sql.append("=");
				sql.append(String.format("#{%s}", keyParamName));
			}
		}
		sql.append("</where>");
		return sql.toString();
	}

	/**
	 * 基于主键更新
	 * 
	 * @param table
	 *            表
	 * @param skipNullValue
	 *            跳过 null 值
	 * @return
	 */
	public static String updateByPrimaryKeys(EntityTable table, boolean skipNullValue) {
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE ");
		sql.append(getTableOrViewName(table));
		sql.append(" ");
		sql.append(updateSetColumns(table, skipNullValue));
		sql.append(wherePrimaryKeys(table));
		return sql.toString();
	}

	/**
	 * 截断表数据
	 * 
	 * @param table
	 *            表
	 * @return
	 */
	public static String truncateByTable(EntityTable table) {
		StringBuilder sql = new StringBuilder();
		sql.append("TRUNCATE ");
		sql.append(getTableOrViewName(table));
		return sql.toString();
	}

	/**
	 * 删除所有
	 * 
	 * @param table
	 *            表
	 * @return
	 */
	public static String deleteByAll(EntityTable table) {
		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM ");
		sql.append(getTableOrViewName(table));
		return sql.toString();
	}

	/**
	 * 删除
	 * 
	 * @param table
	 *            表
	 * @return
	 */
	public static String deleteByPrimaryKey(EntityTable table) {
		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM ");
		sql.append(getTableOrViewName(table));
		sql.append(" ");
		sql.append(wherePrimaryKeys(table));
		return sql.toString();
	}

	/**
	 * 获取所有列
	 * 
	 * @param table
	 * @return
	 */
	public static String getAllColumns(EntityTable table) {
		StringBuilder sql = new StringBuilder();
		int index = 0;
		for (EntityColumn column : table.getColumns()) {
			if (index > 0) {
				sql.append(",");
			}
			sql.append(column.getColumnName());
			index++;
		}
		return sql.toString();
	}

	/**
	 * 基于主键查询
	 * 
	 * @param table
	 *            表
	 * @return
	 */
	public static String selectByPrimaryKey(EntityTable table) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append(getAllColumns(table));
		sql.append(" FROM ");
		sql.append(getTableOrViewName(table));
		sql.append(wherePrimaryKeys(table));
		return sql.toString();
	}

	/**
	 * 全部查询
	 * 
	 * @param table
	 *            表
	 * @return
	 */
	public static String selectAll(EntityTable table) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append(getAllColumns(table));
		sql.append(" FROM ");
		sql.append(getTableOrViewName(table));
		if (!StringUtils.isNullOrBlank(table.getOrderByClause())) {
			sql.append(" ORDER BY ");
			sql.append(table.getOrderByClause());
		}
		return sql.toString();
	}

	/**
	 * 全部查询记录数
	 * 
	 * @param table
	 *            表
	 * @return
	 */
	public static String selectAllCount(EntityTable table) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT(*) FROM ");
		sql.append(getTableOrViewName(table));
		return sql.toString();
	}

	/**
	 * 按条件查询的sql语句
	 * 
	 * @param table
	 * @return
	 */
	public static String selectByCondition(EntityTable table) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append(getAllColumns(table));
		sql.append(" FROM ");
		sql.append(getTableOrViewName(table));
		sql.append(" WHERE ");
		sql.append(selectColumns(table, true, true));
		return sql.toString();
	}

	public static String selectColumns(EntityTable table, boolean skipId, boolean skipNullValue) {
		StringBuilder sql = new StringBuilder();
		sql.append("<trim prefix=\"(\" suffix=\")\" suffixOverrides=\"and\">");
		for (EntityColumn column : table.getColumns()) {
			if (skipNullValue) {
				sql.append(MybatisXmlUtils.createIfTestValueNotNullNode(column, false, column.getColumnName(), false));
			} else {
				sql.append(column.getColumnName());
				sql.append(",");
			}
		}
		sql.append("</trim>");

		return sql.toString();
	}

}
